package com.fcscs.cservice.dao.hibernate;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.fcscs.core.dao.ConnectionManager;
import com.fcscs.core.domain.BaseEntity.DBLoc;
import com.fcscs.cservice.common.CServiceCommonEnums.BookingStatus;
import com.fcscs.cservice.common.CServiceCommonEnums.BookingViewOrderBy;
import com.fcscs.cservice.common.CServiceCommonEnums.GuestProfileType;
import com.fcscs.cservice.dao.BookingDao;
import com.fcscs.cservice.domain.BookingBase;
import com.fcscs.cservice.domain.Country;
import com.fcscs.cservice.domain.GuestProfile;
import com.fcscs.cservice.domain.Restaurant;
import com.fcscs.cservice.domain.model.BookingIdAndMessage;
import com.fcscs.cservice.domain.model.BookingIdAndStatus;
import com.fcscs.cservice.domain.model.BookingUpdate;
import com.fcscs.cservice.domain.model.BookingView;
import com.fcscs.cservice.domain.model.NonInHouseGuestProfile;

@SuppressWarnings("unchecked")
@Repository("bookingDao")
public class BookingDaoHibernate extends BaseDaoHibernate<BookingBase> implements BookingDao {

	private static DBLoc BKDBLoc = new BookingBase().getDBLoc();
	private static String UPDATE_BOOKING_SATUS = "UPDATE booking b set b.booking_status= :status, b.updated_by=:updatedBy,b.updated=:updateDate WHERE b.id= :bookingId";
	
	private static String BOOKING_HISTORY = "FROM BookingView bv WHERE bv.orgId= :orgId AND" +
	" bv.propertyId= :propId AND bv.guestProfileId = :guestId AND bv.reservationDate >= :resconfigDate";

	private static String BOOKING_HI_COUNT = "select count(*) from BookingView bv where bv.orgId= :orgId and" +
	" bv.propertyId = :propId and bv.guestProfileId= :guestId and bv.reservationDate >= :resconfigDate";
	
	private static final String ADV_FIND_BOOKING_V = "From BookingView bv where bv.orgId= :orgId and bv.propertyId = :propId ";
	private static final String ADV_BOOKING_FIND_COUNT= "select count(*) from BookingView bv where bv.orgId= :orgId and bv.propertyId = :propId ";
	
	private static final String SIM_FIND_BOOKING_V = "From BookingView bv where bv.orgId= :orgId and bv.propertyId = :propId and bv.reservationDate >=:resDate ";
	private static final String SIM_BOOKING_FIND_COUNT= "select count(*) from BookingView bv where bv.orgId= :orgId and bv.propertyId = :propId and bv.reservationDate >=:resDate ";
	

	@Autowired
	private ConnectionManager connMgr;

	@Override
	protected Class<BookingBase> getCls() {
		return BookingBase.class;
	}

	@Override
	protected ConnectionManager getConnectionManager() {
		return connMgr;
	}

	@Override
	protected DBLoc getDBLoc() {
		return BKDBLoc;
	}

	@Override
	public <E extends BookingBase> E get(Class<E> cls, long id) {
		return (E) getConnectionManager().getCurrentSession(getDBLoc()).load(cls, id);
	}

	@Override
	public <E extends BookingBase> E save(E loc) {
		return (E) getConnectionManager().getCurrentSession(BKDBLoc).save(loc);
	}
	
	@Override
	public List<BookingView> simFindBookings(long orgId, Long propId,Long restaurantId, String term,Date reservationDate, int start, int resultCnt) {
		
		Query que = simFindBookingViews(orgId, propId, restaurantId, term, reservationDate, SIM_FIND_BOOKING_V);
		que.setFirstResult(start);
		que.setMaxResults(resultCnt);
		
		return que.list();
	}
	
	@Override
	public int simFindBookingsCnt(long orgId, Long propId,Long restaurantId, String term,Date reservationDate){

		Query que = simFindBookingViews(orgId, propId, restaurantId, term, reservationDate, SIM_BOOKING_FIND_COUNT);

		return ((Number) que.uniqueResult()).intValue();
	}

	private Query simFindBookingViews(long orgId, Long propId,
			Long restaurantId, String term, Date resertvationDate, String sql) {
		String sqlC = sql+
					(restaurantId != null? "and bv.restaurantId= :restaurantId ":"")+
					(term != null ? "and CONCAT(bv.firstName,bv.lastName,bv.phoneNo) like :term":"");
		Query que = connMgr.getCurrentSession(BKDBLoc).createQuery(sqlC);
		que.setLong("orgId", orgId);
		que.setLong("propId",propId);
		que.setTimestamp("resDate",resertvationDate);
		if(restaurantId != null){
			que.setLong("restaurantId", restaurantId);
		}
		if(term != null){
			que.setString("term", "%"+term+"%");
		}
		return que;
	}
	
	@Override
	public List<BookingView> findBookings(long orgId, Long propId, Long restaurantId, String term,
			String firstName,String lastName, String mobileNo,GuestProfileType guestType,
			Date reservationDate, List<BookingStatus> statuses,
			Date startDate,Date endDate,int start,int resultCnt, BookingViewOrderBy orderBy, boolean sortDesc) {
		
		Query que = findBookingviews(orgId, propId, restaurantId, term,firstName,
					lastName, mobileNo, guestType, reservationDate, statuses, startDate, endDate, ADV_FIND_BOOKING_V);
		que.setFirstResult(start);
		que.setMaxResults(resultCnt);
		
		return que.list();
	}

	@Override
	public int findBookingsCnt(long orgId, Long propId, Long restaurantId, String term, String firstName,
						String lastName, String mobileNo, GuestProfileType guestType, Date reservationDate,
						List<BookingStatus> statuses,Date startDate,Date endDate) {
		Query que = findBookingviews(orgId, propId, restaurantId, term, firstName,
				lastName, mobileNo, guestType, reservationDate, statuses, startDate, endDate, ADV_BOOKING_FIND_COUNT);
		
		return ((Number) que.uniqueResult()).intValue();
	}

	private Query findBookingviews(long orgId, Long propId, Long restaurantId,
			String term,String firstName, String lastName, String mobileNo,GuestProfileType guestType,
			Date reservationDate, List<BookingStatus> statuses, Date stDate,Date endDate,String sql) {
		//TODO: term unUsed
//***********************************************************************************			
		String sqlC = sql +
					(reservationDate != null ? "and bv.reservationDate >=:resDate ":"") +
					(restaurantId != null? "and bv.restaurantId= :restaurantId ":"") +
					((statuses != null && statuses.size()!=0) ? "and bv.status in (:statuses) ":"") +
					(firstName != null ? "and bv.firstName like :firstName ":"") +
					(lastName != null ? "and bv.lastName like :lastName ":"") +
					(mobileNo != null ? "and bv.phoneNo like :mobile ":"") +
					(guestType != null ? "and bv.guestType=:guestType ":"")+
					(stDate != null ? "and bv.reservationDate >=:startDate ":"") +
					(endDate != null ? "and bv.reservationDate <=:endDate":"")
					;
			//		(stDate != null ? "and bv.created >=:startDate ":"") +
			//		(endDate != null ? "and bv.created <=:endDate":"")
		Query que = connMgr.getCurrentSession(BKDBLoc).createQuery(sqlC);
		que.setLong("orgId", orgId);
		que.setLong("propId",propId);
		if(reservationDate != null){
			que.setTimestamp("resDate", reservationDate);
		}
		if(restaurantId != null){
			que.setLong("restaurantId", restaurantId);
		}
		if(firstName != null){
			que.setString("firstName", "%"+firstName+"%");
		}
		if(lastName != null){
			que.setString("lastName", "%"+lastName+"%");
		}
		if(mobileNo != null){
			que.setString("mobile", "%"+mobileNo+"%");
		}
		if(guestType != null){
			que.setString("guestType", guestType.toString());
		}
		//c_service need ,ws disable
		if(stDate != null){
			que.setTimestamp("startDate", stDate);
		}
		if(endDate != null){
			que.setTimestamp("endDate", endDate);
		}
		if(statuses != null && statuses.size() != 0){
			List<String> l = new ArrayList<String>();
			for(BookingStatus s : statuses){
				l.add(s.toString());
			}
			que.setParameterList("statuses", l);
		}
		return que;
	}
	
	@Override
	public List<BookingIdAndMessage> updateBookingStatus(long orgId,Long propId,long userId,List<BookingIdAndStatus> idstatus) {
		List<BookingIdAndMessage> list = new ArrayList<BookingIdAndMessage>();
		for(BookingIdAndStatus bs : idstatus){
			BookingIdAndMessage ba = new BookingIdAndMessage();
			ba.setBookingId(bs.getBookingId());
			try {
				SQLQuery q = connMgr.getCurrentSession(BKDBLoc).createSQLQuery(UPDATE_BOOKING_SATUS);
				q.setString("status", bs.getBookingStatus());
				q.setLong("bookingId", bs.getBookingId());
				q.setLong("updatedBy", userId);
				q.setTimestamp("updateDate", new Date());
				q.executeUpdate();
				ba.setStatus(Boolean.TRUE);
			} catch (Exception e) {
				ba.setStatus(Boolean.FALSE);
			}
			list.add(ba);
		}
		return list;
	}

	@Override
	public int getBookingHistoryCnt(long orgId,Long propId, long guestId) {
		Query query = connMgr.getCurrentSession(BKDBLoc).createQuery(BOOKING_HI_COUNT);
		query.setLong("orgId", orgId);
		query.setLong("propId", propId);
		query.setLong("guestId", guestId);
		//TODO get config reservation date
		query.setString("resconfigDate", "2012-03-18 15:00:00");
		return ((Number) query.uniqueResult()).intValue();
	}
	
	@Override
	public List<BookingView> getBookingHistory(long orgId,Long propId, long guestId,int start, int resultCnt) {
		Query query = connMgr.getCurrentSession(BKDBLoc).createQuery(BOOKING_HISTORY);
		query.setLong("orgId", orgId);
		query.setLong("propId", propId);
		query.setLong("guestId", guestId);
		//TODO get config reservation date
		query.setString("resconfigDate", "2012-03-18 15:00:00");
		query.setFirstResult(start);
		query.setMaxResults(resultCnt);
		return query.list();
	}

	@Override
	public BookingView createBooking(BookingBase bkBase,NonInHouseGuestProfile nonInGuest) {
		if(nonInGuest != null && bkBase.getGuestProfileId() == 0){
			GuestProfile gp = new GuestProfile();
			gp.setOrgId(bkBase.getOrgId());
			gp.setPropertyId(bkBase.getPropertyId());
			gp.setProfileNumber("");
			gp.setEmail(nonInGuest.getEmailAddress());
			gp.setFirstName(nonInGuest.getFirstName());
			gp.setLastName(nonInGuest.getLastName());
			gp.setGenderMale(nonInGuest.getGender());
			gp.setMobile(nonInGuest.getPhoneNumber());
			gp.setCreatedBy(bkBase.getCreatedBy());
			connMgr.getCurrentSession(BKDBLoc).save(gp);
			bkBase.setGuestType(GuestProfileType.NON_IN_HOUSE_GUEST);
			bkBase.setGuestProfileId(gp.getId());
		}else if(bkBase.getGuestProfileId() > 0 && nonInGuest == null){
			bkBase.setGuestType(GuestProfileType.IN_HOUSE_GUEST);
		}
		connMgr.getCurrentSession(BKDBLoc).save(bkBase);
		BookingView bv = (BookingView) connMgr.getCurrentSession(BKDBLoc).get(BookingView.class,bkBase.getId());
		return bv;
	}

	@Override
	public BookingView updateBooking(BookingUpdate bu) {
		if(bu.getArrivalDate() != null || bu.getNotify()!=null || bu.getSmoking() != null ||
				bu.getBookingStatus() != null || bu.getNoOfGuest() != null || bu.getNote() != null ||
				bu.getReservationDate() != null || bu.getDepartureDate() != null){
			updateBookingBase(bu);
		}
		if(bu.getGuestFirstName() != null || bu.getGuestLastName()!=null || bu.getGender() != null || 
				bu.getPhoneNumber() != null || bu.getEamil() != null || bu.getTitleId() != null){
			updateGuestProAboutBk(bu);
		}
		BookingView bv = (BookingView) connMgr.getCurrentSession(BKDBLoc).get(BookingView.class, bu.getBookingId());
		return bv;
	}
	
	private String buildBkupGuestCon(BookingUpdate bu){
		
		StringBuffer sb = new StringBuffer();
		sb.append("Update GuestProfile gp set gp.createdBy=:createBy,gp.updated=:updateDate ");
		//TODO: Add titlte id in guestProfile 
		if(bu.getGuestFirstName()!= null ){
			sb.append(",gp.firstName=:firstName ");
		}
		if(bu.getGuestLastName() != null){
			sb.append(",gp.lastName=:lastName ");
		}
		if(bu.getGender() != null ){
			sb.append(",gp.genderMale=:gender ");
		}
		if(bu.getPhoneNumber() != null ){
			sb.append(",gp.mobile=:phoneNo ");
		}
		if(bu.getEamil() != null){
			sb.append(",gp.email=:email ");
		}
		if(bu.getTitleId() != null){
			sb.append(",gp.titleId=:titleId ");
		}
		sb.append("where gp.id=:guestId");
		return sb.toString();
	}
	
	private void updateGuestProAboutBk(BookingUpdate bu){

		Query query=connMgr.getCurrentSession(BKDBLoc).createQuery(buildBkupGuestCon(bu));
		query.setLong("createBy", bu.getCreatedBy());
		query.setTimestamp("updateDate", new Date());
		if (bu.getGuestFirstName() != null) {
			query.setString("firstName", bu.getGuestFirstName());
		}
		if (bu.getGuestLastName() != null) {
			query.setString("lastName", bu.getGuestLastName());
		}
		if (bu.getGender() != null) {
			query.setBoolean("gender", bu.getGender());
		}
		if (bu.getPhoneNumber() != null) {
			query.setString("phoneNo", bu.getPhoneNumber());
		}
		if ( bu.getEamil() != null) {
			query.setString("email", bu.getEamil());
		}
		if (bu.getTitleId() != null) {
			query.setLong("titleId", bu.getTitleId());
		}
		query.setLong("guestId",bu.getGuestId());
		query.executeUpdate();
	}
	
	private String buildUpBkbaseCon(BookingUpdate bu){
		StringBuffer sb = new StringBuffer();
		sb.append("Update BookingBase bb Set bb.createdBy=:createBy,bb.updated=:updateDate ");
		if(bu.getReservationDate()!= null){
			sb.append(",bb.reservationDate=:resDate ");
		}
		if(bu.getNotify()!=null){
			sb.append(",bb.notify=:notify ");
		}
		if(bu.getSmoking() != null){
			sb.append(",bb.smoking=:smoking ");
		}
		if(bu.getBookingStatus() != null){
			sb.append(",bb.bookingStatus=:status "); 
		}
		if(bu.getNoOfGuest()!= null){
			sb.append(",bb.numberOfGuest=:noOfguest ");
		}
		if(bu.getNote() != null ){
			sb.append(",bb.note=:note ");
		}
		if(bu.getArrivalDate()!= null){
			sb.append(",bb.arrivalDate=:arrivalDate ");
		}
		if(bu.getDepartureDate()!=null){
			sb.append(",bb.departureDate=:deptDate "); 
		}
		sb.append("where bb.id=:bkId");
		return sb.toString();
	}
	
	private void updateBookingBase(BookingUpdate bu){

		Query query=connMgr.getCurrentSession(BKDBLoc).createQuery(buildUpBkbaseCon(bu));
		query.setLong("createBy", bu.getCreatedBy());
		query.setTimestamp("updateDate", new Date());
		if (bu.getReservationDate() != null) {
			query.setTimestamp("resDate", bu.getReservationDate());
		}
		if (bu.getNotify() != null) {
			query.setString("notify", bu.getNotify());
		}
		if (bu.getSmoking() != null) {
			query.setBoolean("smoking", bu.getSmoking());
		}
		if ( bu.getBookingStatus() != null) {
			query.setString("status", bu.getBookingStatus().toString());
		}
		if ( bu.getNoOfGuest() != null) {
			query.setInteger("noOfguest", bu.getNoOfGuest());
		}
		if (bu.getNote() != null) {
			query.setString("note", bu.getNote());
		}
		if (bu.getArrivalDate() != null) {
			query.setTimestamp("arrivalDate", bu.getArrivalDate());
		}
		if (bu.getDepartureDate() != null) {
			query.setTimestamp("deptDate", bu.getDepartureDate());
		}
		query.setLong("bkId",  bu.getBookingId());
		query.executeUpdate();
	}

	@Override
	public List<Country> seedCountry(long orgId,Long propId,String path) {
		Session session = connMgr.getCurrentSession(BKDBLoc);
		List<Country>  list = null;
		String sql = "FROM Country c WHERE c.orgId=:orgId AND c.propertyId=:propId";
		Query que = session.createQuery(sql);
		que.setLong("orgId", orgId);
		que.setLong("propId",propId);
		if(que.list().size() != 0){
			System.out.println("---------------------------------------------------have input data----------");
		}else{
			list = parseXLS2007(path);
			saveDataByHibernate(orgId,propId,list);
		}
		return list;
	}

	private void saveDataByHibernate(long orgId,Long propId,List<Country>  list){
		
		Session session = connMgr.getCurrentSession(BKDBLoc);
		Transaction tx = session.beginTransaction(); 
		int i= 0 ;
		for(Country xd : list){
			i++;
			xd.setOrgId(orgId);
			xd.setPropertyId(propId);
			xd.setPriority(0);
			session.save(xd);
			if(i%100==0){
				session.flush();
				session.clear();
			}
		}
		tx.commit();
		session.close();
	}

	public List<Country> parseXLS2007(String fileName){

		System.out.println(fileName+"-------------------------------------------servlet name");
		List<Country>  list = new ArrayList<Country>();
		XSSFWorkbook xwb = null;   
		try{   
			xwb = new XSSFWorkbook(fileName);   
			for (int numSheet = 0; numSheet < xwb.getNumberOfSheets(); numSheet++) {   
				XSSFSheet xSheet = xwb.getSheetAt(numSheet);   
				if (xSheet == null) {   
					continue;   
				}   
				for (int rowNum = 1; rowNum <= xSheet.getLastRowNum(); rowNum++) {   
					XSSFRow xRow = xSheet.getRow(rowNum);   
					if (xRow == null) {   
						continue;   
					}
					Country model = new Country();
					for (int cellNum = 0; cellNum <= xRow.getLastCellNum(); cellNum++) {  
						XSSFCell xCell = xRow.getCell(cellNum);   
						if (xCell == null || "".equals(xCell)) {
						}else{
							if(cellNum == 0 ){
								model.setCode(xCell.getStringCellValue());
							}else if(cellNum == 1 ){
								model.setCountry(xCell.getStringCellValue());
							}else if(cellNum == 2 ){
								model.setCountrySecLang(xCell.getStringCellValue());
							}else if(cellNum == 3){
								model.setCountryThirdLang(xCell.getStringCellValue());
							}
						}      
					}
					list.add(model);
				}   
			}
		}catch(Exception e){   
			e.printStackTrace();
		}   
		return list;   
	}   

	@Override
	public InputStream readImageFromDataBase(){

		Session session =connMgr.getCurrentSession(getDBLoc());
//		SQLQuery query = session.createSQLQuery("select logo from restaurant r where id=1"); 
////		Query query = session.createQuery("select logo from restaurant r where id=1"); 
//		Blob blob = (Blob) query.uniqueResult();
//		blob.getBinaryStream();
		
		Restaurant restaurnat=(Restaurant) session.get(Restaurant.class, Long.valueOf(1));
		byte[] buffer = restaurnat.getLogo();
		try {
			return  new ByteArrayInputStream(buffer);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
}
